﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[tblWH_PerfmonHourlySummary]
#-- Purpose:		Store the warehouse copy of perfmon hourly summary
#--	Last Update:	03/21/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE TABLE [dbo].[tblWH_PerfmonHourlySummary]
(
	perf_id int IDENTITY(1, 1) NOT NULL,
	date_inserted datetime NOT NULL,
	[server_ssd_id] int NOT NULL,
	buffer_cache_hit_ratio_AVG float NULL,
	buffer_cache_hit_ratio_STDEVP float NULL,
	page_life_expectancy_AVG int NULL,
	page_life_expectancy_STDEVP int NULL,
	logins_per_sec_AVG float NULL,
	logins_per_sec_STDEVP float NULL,
	logouts_per_sec_AVG float NULL,
	logouts_per_sec_STDEVP float NULL,
	user_connections_AVG int NULL,
	user_connections_STDEVP int NULL,
	optimizer_memory_KB int NULL,
	sql_cache_memory_KB int NULL,
	target_server_memory_KB int NULL,
	total_server_memory_KB int NULL,
	sql_compilations_per_sec_AVG float NULL,
	sql_compilations_per_sec_STDEVP float NULL,
	sql_recompilations_per_sec_AVG float NULL,
	sql_recompilations_per_sec_STDEVP float NULL,
	batch_requests_per_sec_AVG float NULL,
	batch_requests_per_sec_STDEVP float NULL,
	number_of_deadlocks_per_sec_AVG float NULL,
	number_of_deadlocks_per_sec_STDEVP float NULL,
	average_wait_time_ms_AVG float NULL,
	average_wait_time_ms_STDEVP float NULL,
	lock_waits_per_sec_AVG float NULL,
	lock_waits_per_sec_STDEVP float NULL,
	lock_wait_time_ms_AVG float NULL,
	lock_wait_time_ms_STDEVP float NULL,
	full_scans_per_sec_AVG float NULL,
	full_scans_per_sec_STDEVP float NULL,
	lazy_writes_per_sec_AVG float NULL,
	lazy_writes_per_sec_STDEVP float NULL,
	page_reads_per_sec_AVG float NULL,
	page_reads_per_sec_STDEVP float NULL,
	page_writes_per_sec_AVG float NULL,
	page_writes_per_sec_STDEVP float NULL,
	checkpoint_pages_per_sec_AVG float NULL,
	checkpoint_pages_per_sec_STDEVP float NULL,
	page_splits_per_sec_AVG float NULL,
	page_splits_per_sec_STDEVP float NULL,
	transactions_per_sec_AVG float NULL,
	transactions_per_sec_STDEVP float NULL,
	cpu_busy_AVG float NULL,
	cpu_busy_STDEVP float NULL,
	io_busy_AVG float NULL,
	io_busy_STDEVP float NULL,
	CONSTRAINT [PK_tblWH_PerfmonHourlySummary] PRIMARY KEY CLUSTERED ([perf_id] ASC),
	CONSTRAINT [FK_tblWH_PerfmonHourlySummary_tblMSX_server_discovery] FOREIGN KEY ([server_ssd_id]) REFERENCES [tblMSX_server_discovery] ([ssd_id])
)
GO
CREATE NONCLUSTERED INDEX [IX_tblWH_PerfmonHourlySummary_ServerDate] ON [dbo].[tblWH_PerfmonHourlySummary]
(
	server_ssd_id,
	date_inserted
)
GO